package com.aier.user.common;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Time;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.aier.admin.entity.SysCity;
import com.aier.common.common.Constant;
import com.aier.school.entity.SchoolGradeAttendance;


public class UserExcleUtil {
    /**
     * 默认类型 0卡头 AA 1卡身 0000 2卡尾 1234 3十六进制ic内卡号
     */
    public static final int TYPE_DEFAULT = 0;
    public static final String EXCEL_VERSION = "v0.1";
    public static final String SHEETNAME_BASE = "base";
    public static final String SHEETNAME_SCHOOL = "school";
    public static final String SHEETNAME_TYPE = "teacher_type";
    public static final String SHEETNAME_TEACHER = "teacher";
    public static final String SHEETNAME_CLASS = "classes";
    public static final String SHEETNAME_ICCARD = "ic";
    public static final String[] BASE_TITLE = new String[]{"数据版本"};
    public static final String[] SCHOOL_TITLE = new String[]{"学校名称","管理员账号","学校地址","网址","联系电话","电子邮箱"};
    public static final String[] TYPE_TITLE = new String[]{"职务","上午上班时间","上午下班时间","下午上班时间","下午下班时间","每周上班周期"};
    public static final String[] IC_TITLE = new String[]{"卡号"};
    public static final String[] CLASS_TITLE = new String[]{"班级名称"};
    public static final String[] TEACHER_TITLE = new String[]{"账号","密码","卡号", "姓名", "职务", "身份证", "联系电话","联系邮箱","联系地址","班级" };
    //public static final String SAVEPATH = "E:\\proxy_school.xls";
    public static List<String> duties; 
    public static List<String> ic;
    public static List<String> classes;


    //获取所有标签页的信息
    public static Map<String,List<List<String>>> parseExcel(File f, String fileContentType) {
        Workbook wb = null;
        Map<String,List<List<String>>> mapExcle = new HashMap<String,List<List<String>>>();
        try {
            if ("application/vnd.ms-excel".equals(fileContentType)) {
                wb = new HSSFWorkbook(new FileInputStream(f));
            } else if ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                    .equals(fileContentType)) {
                try {
                    wb = new XSSFWorkbook(new FileInputStream(f));
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }else{
                return null;
            }
            mapExcle = parseExcelDefault(wb);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return mapExcle;
    }

    private static Map<String,List<List<String>>> parseExcelDefault(Workbook wb) {
        Map<String,List<List<String>>> mapExcle = new HashMap<String,List<List<String>>>();
        List<List<String>> sheetList = new ArrayList<List<String>>();
        String sheetName = "";
        String version = "";
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            sheetName = sheet.getSheetName();
            if(SHEETNAME_BASE.equals(sheetName)){
                List<List<String>> baseSheetList = parseExcelBaseSheel(sheet);
                version = baseSheetList.get(0).get(1);
                break;
            }
        }
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            sheetName = sheet.getSheetName();
            if(EXCEL_VERSION.equals(version)){
                if(SHEETNAME_BASE.equals(sheetName)){
                    continue;
                }
                sheetList = parseExcelDefaultSheel(sheet,sheetName);
            }
            mapExcle.put(sheetName, sheetList);
        }
        return mapExcle;
    }
    

    //根据不同的标签名调用不同的解析方法
    private static List<List<String>> parseExcelDefaultSheel(Sheet sheet,String sheetName) {
        List<List<String>> sheetList = new ArrayList<List<String>>();
        if(SHEETNAME_SCHOOL.equals(sheetName)){
            sheetList = parseExcelSchoolSheel(sheet);
        }
        if(SHEETNAME_TYPE.equals(sheetName)){
            sheetList = parseExcelTypeSheel(sheet);
        }
        if(SHEETNAME_TEACHER.equals(sheetName)){
            sheetList = parseExcelTeacherSheel(sheet);
        }
        if(SHEETNAME_CLASS.equals(sheetName)){
            sheetList = parseExcelClassSheel(sheet);
        }
        if(SHEETNAME_ICCARD.equals(sheetName)){
            sheetList = parseExcelIcCardSheel(sheet);
        }        
        return sheetList;
    }

    //解析base页
    private static List<List<String>> parseExcelBaseSheel(Sheet sheet) {
        List<List<String>> rowList = new ArrayList<List<String>>();    
        List<String> cellList = new ArrayList<String>();
        Row row = sheet.getRow(0);
        cellList.add(changeString(row.getCell(0)));//数据版本
        cellList.add(changeString(row.getCell(1)));//版本号
        rowList.add(cellList);

        return rowList;
    }
    
    //解析school信息页
    private static List<List<String>> parseExcelSchoolSheel(Sheet sheet) {
        List<List<String>> rowList = new ArrayList<List<String>>();
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            List<String> cellList = new ArrayList<String>();
            Row row = sheet.getRow(j);
            if(null!=row){
                for (int i=0;i<Constant.num_6;i++){
                    cellList.add(changeString(row.getCell(i)));
                }
                rowList.add(cellList);
            }
        }
        return rowList;
    }
    
    //解析职务页
    private static List<List<String>> parseExcelTypeSheel(Sheet sheet) {
        List<List<String>> rowList = new ArrayList<List<String>>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            List<String> cellList = new ArrayList<String>();
            Row row = sheet.getRow(j);
            if (null!=row&&null!=row.getCell(0)&&!"".equals(changeString(row.getCell(0)))){
                for (int i=0;i<Constant.num_6;i++){
                    if(i==0||i==Constant.num_5){
                        cellList.add(changeString(row.getCell(i)));
                    }else{
                        if(null!=row.getCell(i)){
                            row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                        }
                        cellList.add(changeString(row.getCell(i)));
                    }
                }
                rowList.add(cellList);                
            }else{
                continue;
            }
        }
        return rowList;
    }
    
    //解析teacher信息页
    private static List<List<String>> parseExcelTeacherSheel(Sheet sheet) {
        List<List<String>> rowList = new ArrayList<List<String>>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            List<String> cellList = new ArrayList<String>();
            Row row = sheet.getRow(j);
            if (null!=row&&null!=row.getCell(0)&&!"".equals(changeString(row.getCell(0)))){
                for (int i=0;i<Constant.num_10;i++){
                    cellList.add(changeString(row.getCell(i)));
                }
                rowList.add(cellList);
            }else{
                continue;
            }
        }
        return rowList;
    }
    
    //解析班级页
    private static List<List<String>> parseExcelClassSheel(Sheet sheet) {
        List<List<String>> rowList = new ArrayList<List<String>>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            List<String> cellList = new ArrayList<String>();
            Row row = sheet.getRow(j);
            if (null!=row&&null!=row.getCell(0)&&!"".equals(changeString(row.getCell(0)))){
                cellList.add(changeString(row.getCell(0)));
                rowList.add(cellList);
            }else{
                continue;
            }
        }
        return rowList;
    }
    
    //解析ic卡页
    private static List<List<String>> parseExcelIcCardSheel(Sheet sheet) {
        List<List<String>> rowList = new ArrayList<List<String>>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            List<String> cellList = new ArrayList<String>();
            Row row = sheet.getRow(j);
            if (null!=row&&null!=row.getCell(0)&&!"".equals(changeString(row.getCell(0)))){
                cellList.add(changeString(row.getCell(0)));
                rowList.add(cellList);
            }else{
                continue;
            }
        }
        return rowList;
    }
    
    private static String changeString(Cell cell) {
        if (null == cell) {
            return null;
        }
        String tmp = null;
        try {
            tmp = cell.getStringCellValue();
        } catch (IllegalStateException e) {
            tmp = String.valueOf((long)cell.getNumericCellValue());
        }
        return tmp;
    }
    
    //解析学校的地址(省,市,区,镇)
    public static int getSchoolCity(List<SysCity> sysCities,List<String> schoolList){
        int returnValue = -1;
        for(int i=1;i<Constant.num_5;i++){
            String cityName = schoolList.get(i);
            for(SysCity sysCity:sysCities){
                if(sysCity.getName().equals(cityName)){
                    returnValue = sysCity.getCid();
                }
            }        
        }
        return returnValue;
    }
    
    
    //解析职务标签页的数据
    public static List<SchoolGradeAttendance> getSGAList(List<String> typesList) throws ParseException{
        List<SchoolGradeAttendance> sgas = new ArrayList<SchoolGradeAttendance>();
        SimpleDateFormat df2 = new SimpleDateFormat("HH:mm");
        Time ast = null, aet = null, pst = null, pet = null;
        String amStartTime,amEndTime,pmStartTime,pmEndTime;
        int type = 0;
        amStartTime = typesList.get(1);
        amEndTime = typesList.get(2);
        pmStartTime = typesList.get(Constant.num_3);
        pmEndTime = typesList.get(Constant.num_4);
        String dateType = typesList.get(Constant.num_5).substring(1);
        if (!"".equals(amStartTime)&&null!=amStartTime){
            ast = new Time(df2.parse(amStartTime).getTime());
        }
        if (!"".equals(amEndTime)&&null!=amEndTime){
            aet = new Time(df2.parse(amEndTime).getTime());
        }
        if (!"".equals(pmStartTime)&&null!=pmStartTime){
            pst = new Time(df2.parse(pmStartTime).getTime());
        }
        if (!"".equals(pmEndTime)&&null!=pmEndTime){
            pet = new Time(df2.parse(pmEndTime).getTime());
        }

        for (int i = 0; i < dateType.length(); i++) {
            SchoolGradeAttendance sga = new SchoolGradeAttendance();
            sga.setSadate(ast);
            sga.setEadate(aet);
            sga.setSpdate(pst);
            sga.setEpdate(pet);    
            if("2222222".equals(dateType)){
                break;
            }
            if ("1".equals(dateType.substring(i, i+1))){
                type = i+1;
            }else{
                continue;
            }
            switch (type) {
                case 1:
                    sga.setType(UserWeekData.GRADE_TYPE_SUNDAY);
                    break;
                case 2:
                    sga.setType(UserWeekData.GRADE_TYPE_MONDAY);
                    break;
                case Constant.num_3:
                    sga.setType(UserWeekData.GRADE_TYPE_TUESDAY);
                    break;
                case Constant.num_4:
                    sga.setType(UserWeekData.GRADE_TYPE_WESDNESDAY);
                    break;
                case Constant.num_5:
                    sga.setType(UserWeekData.GRADE_TYPE_THURDAY);
                    break;
                case Constant.num_6:
                    sga.setType(UserWeekData.GRADE_TYPE_FRIDAY);
                    break;
                default:
                    sga.setType(UserWeekData.GRADE_TYPE_SATURDAY);
                    break;
            }
            sgas.add(sga);        
        }        
        return sgas;
    }
    
    //检查标签页的指定cell列的值是否有重复的
    public static String checkRepeat(List<List<String>> rowList,int cellNum){
        String v1,v2;
        for(int i=0;i<rowList.size()-1;i++){
            v1 = rowList.get(i).get(cellNum);
            for(int j=i+1;j<rowList.size();j++){
                v2 = rowList.get(j).get(cellNum);
                if(null != v1 && !"".equals(v1) && v1==v2){
                    return v1;
                }
            }
        }
        return "";
    }
    
    //检查rowList页的第cellNum列数据是否都在rowList_in的cellNum_in列的数据范围内
    public static String checkInCell(List<List<String>> rowList,int cellNum,List<List<String>> rowList_in,int cellNum_in){
        String v1,v2;
        boolean flag;
        for(int i=0;i<rowList.size();i++){
            flag = false;
            v1 = rowList.get(i).get(cellNum);
            for(int j=0;j<rowList_in.size();j++){
                v2 = rowList_in.get(j).get(cellNum_in);
                if(v2.equals(v1) || null==v1 || "".equals(v1)){
                    flag = true;
                    break;
                }
            }
            if(!flag){
                return v1;
            }
        }        
        return "";
    }
    
    
    /**
     * 通过map生成工作簿
     * @param map            要写入的数据
     */
    public static HSSFWorkbook  createExcel(Map<String,List<Object>> map){
        //String sheetName;
        //生成一个工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet;
//        for (Iterator<String> iterator = map.keySet().iterator(); iterator.hasNext();) {
//            sheetName = iterator.next();
//            sheet = wb.createSheet(sheetName);
//            // 设置表格默认列宽度为10个字节
//            sheet.setDefaultColumnWidth(10);      
//            writeToSheet(sheetName, sheet, map.get(sheetName));
//        }

            
        sheet = wb.createSheet(SHEETNAME_SCHOOL);
        writeToSheet(SHEETNAME_SCHOOL, sheet, map.get(SHEETNAME_SCHOOL));    
    
        sheet = wb.createSheet(SHEETNAME_TYPE);
        writeToSheet(SHEETNAME_TYPE, sheet, map.get(SHEETNAME_TYPE));
    

        sheet = wb.createSheet(SHEETNAME_ICCARD);
        writeToSheet(SHEETNAME_ICCARD, sheet, map.get(SHEETNAME_ICCARD));
    
    
        sheet = wb.createSheet(SHEETNAME_CLASS);
        writeToSheet(SHEETNAME_CLASS, sheet, map.get(SHEETNAME_CLASS));
    

        sheet = wb.createSheet(SHEETNAME_TEACHER);
        writeToSheet(SHEETNAME_TEACHER, sheet, map.get(SHEETNAME_TEACHER));
    
        sheet = wb.createSheet(SHEETNAME_BASE);
        writeToSheet(SHEETNAME_BASE, sheet, map.get(SHEETNAME_BASE));
        
        return wb;
        
    }
    
    
  /**
    * 把数据    写入当前表中
    * @param sheetName    表名称
    * @param sheet        要操作的表
    * @param content    要写入的数据
    */
    @SuppressWarnings("unchecked")
    private static void writeToSheet(String sheetName,HSSFSheet sheet,List<Object> list){
        HSSFRow row;
        HSSFCell cell;
        List<Object> conList;
        int i,j;
        // 设置表格默认列宽度为10个字节
        sheet.setDefaultColumnWidth(Constant.num_10);
        if(SHEETNAME_BASE.equals(sheetName)){
            row = sheet.createRow(0);
            writeTitleInSheet(sheetName, sheet, BASE_TITLE);
            cell = row.createCell(1);
            if(list != null){
                writeToCell(cell, list.get(0).toString());
            }
        }else if(SHEETNAME_SCHOOL.equals(sheetName)){    
            //writeTitleInSheet(CELLWRITE, sheetName, sheet, SCHOOL_TITLE);
            if(list != null){
                for(i=0,j=0; i<list.size(); i++,j++){                 
                    row = sheet.createRow(j);
                    //写入列标题
                    cell = row.createCell(0);
                    writeToCell(cell, SCHOOL_TITLE[j]);
                    cell = row.createCell(1);                 
                    writeToCell(cell, list.get(i).toString());
                    if(i == 1){
                        cell = row.createCell(2);
                        writeToCell(cell,list.get(++i).toString());
                    }else if(i == Constant.num_3){
                        cell = row.createCell(2);
                        writeToCell(cell,list.get(++i).toString());
                        cell = row.createCell(Constant.num_3);
                        writeToCell(cell,list.get(++i).toString());
                        cell = row.createCell(Constant.num_4);
                        writeToCell(cell,list.get(++i).toString());
                        cell = row.createCell(Constant.num_5);
                        writeToCell(cell,list.get(++i).toString());
                    }
                }
            }else{
                for(j=0;j<SCHOOL_TITLE.length;j++){
                    row = sheet.createRow(j);
                    cell = row.createCell(0);
                    writeToCell(cell, SCHOOL_TITLE[j]);
                }
            }
           
        }else if(SHEETNAME_TYPE.equals(sheetName)){
            duties = new ArrayList<String>(); 
            writeTitleInSheet(sheetName, sheet, TYPE_TITLE);
            if(list != null){
                for(i = 0; i<list.size(); i++){
                    row = sheet.createRow(i+1);
                    conList = (List<Object>) list.get(i);               
                    for(j = 0; j < conList.size(); j++){
                        if(j == 0 && ( !conList.get(j).toString().equals(""))) {
                            duties.add(conList.get(j).toString());
                       
                        }
                        cell = row.createCell(j);
                        writeToCell(cell, conList.get(j).toString());
                    }
                }       
            }
        }else if(SHEETNAME_ICCARD.equals(sheetName)){
            ic = new ArrayList<String>(); 
            writeTitleInSheet(sheetName, sheet, IC_TITLE);
            if(list != null){
                for(i = 0; i<list.size(); i++){
                    row = sheet.createRow(i+1);
                    cell = row.createCell(0);
                    if(!list.get(i).toString().equals("")){
                        ic.add(list.get(i).toString());
                    }
                    writeToCell(cell, list.get(i).toString());
                }
            }
        }else if(SHEETNAME_CLASS.equals(sheetName)){
            classes = new ArrayList<String>(); 
            writeTitleInSheet(sheetName, sheet, CLASS_TITLE);
            if(list != null){
                for(i = 0; i<list.size(); i++){
                    row = sheet.createRow(i+1);
                    cell = row.createCell(0);
                    if(!list.get(i).toString().equals("")){
                        classes.add(list.get(i).toString());
                    }
                    writeToCell(cell, list.get(i).toString());
                }
            }
        }else if(SHEETNAME_TEACHER.equals(sheetName)){
            writeTitleInSheet(sheetName, sheet, TEACHER_TITLE);
            //生成下拉列表
            CellRangeAddressList regions;
            //只对（2,2）单元格有效
            regions = new CellRangeAddressList(1,Constant.num_10000,2,2);
            //creatListView(sheet, regions, ic);               
            regions = new CellRangeAddressList(1,Constant.num_10000,Constant.num_4,Constant.num_4);
            creatListView(sheet, regions, duties);
            regions = new CellRangeAddressList(1,Constant.num_10000,Constant.num_9,Constant.num_9);
            creatListView(sheet, regions, classes);
            //设置特定的列宽
            sheet.setColumnWidth(Constant.num_5, Constant.num_5000);
            sheet.setColumnWidth(Constant.num_6, Constant.num_3500);
            sheet.setColumnWidth(Constant.num_7, Constant.num_7000);
            sheet.setColumnWidth(Constant.num_8, Constant.num_9500);
            if(list != null){
                for(i = 0; i<list.size(); i++){
                    row = sheet.createRow(i+1);
                    conList = (List<Object>) list.get(i);
                    for(j = 0; j < conList.size(); j++){
                        cell = row.createCell(j);
                        writeToCell(cell, conList.get(j).toString());
                    }
                }               
              
            }
        }
       
       
       
    }    
   /**
    * 把list数据做成下拉列表
    * @param sheet        表
    * @param regions    作用的单元格
    * @param list        list数据
    */
    private static void creatListView(HSSFSheet sheet,CellRangeAddressList regions,List<String> list){
        DVConstraint constraint;
        HSSFDataValidation data_validation;
        //把list装换成String数组
        String[] result = new String[list.size()];
        for(int i=0; i<list.size(); i++){
            result[i] = list.get(i);
        }
        constraint = DVConstraint.createExplicitListConstraint(result);
        data_validation = new HSSFDataValidation(regions,constraint);
        sheet.addValidationData(data_validation);
    }
       

       
    /**
     * 把标题栏写入表中
     * @param sheetName     表名
     * @param sheet                要操作的表
     * @param title                表的标题栏
     */
    private static void writeTitleInSheet(String sheetName,HSSFSheet sheet,String[] title){
        HSSFRow row;
        HSSFCell cell; 
        //产生表格标题行
        row = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);      
            writeToCell(cell, title[i]);                   
        }    
    }
       
    /**
     *把数据写入的当前列中                
     * @param cell     要操作的列
     * @param content    要输入的内容
     */
    private static void writeToCell(HSSFCell cell,String content){
        HSSFRichTextString text;
        text = new HSSFRichTextString(content);
        cell.setCellValue(text);     
    }
    
    public static void seeExcel() throws Exception{
        File f = new File("F:\\proxy_school.xls");
        Map<String,List<List<String>>> map = parseExcel(f,"application/vnd.ms-excel");
        List<List<String>> schoolRowList = map.get(SHEETNAME_SCHOOL);
        List<List<String>> typeRowList = map.get(SHEETNAME_TYPE);
        List<List<String>> classRowList = map.get(SHEETNAME_CLASS);
        List<List<String>> teacherRowList = map.get(SHEETNAME_TEACHER);
        List<List<String>> icRowList = map.get(SHEETNAME_ICCARD);

        for(List<String> rowList:schoolRowList){
            for(String cellValue:rowList){
                System.out.print(cellValue+",");
            }
            System.out.println();
        }
        for(List<String> rowList:typeRowList){
            for(String cellValue:rowList){
                System.out.print(cellValue+",");
                getSGAList(rowList);
            }
            System.out.println();
        }
        for(List<String> rowList:classRowList){
            for(String cellValue:rowList){
                System.out.print(cellValue+",");
            }
            System.out.println();
        }
        for(List<String> rowList:teacherRowList){
            for(String cellValue:rowList){
                System.out.print(cellValue+",");
            }
            System.out.println();
        }
        for(List<String> rowList:icRowList){
            for(String cellValue:rowList){
                System.out.print(cellValue+",");
            }
            System.out.println();
        }        
    }
        
    //解析list.toString[,,,]为List<String>
    public static List<String> getStrList(String str){
        List<String> stringList = new ArrayList<String>();
        String[] temp_str = str.substring(1,str.length()).split(",");
        for(int i=0;i<temp_str.length;i++){
            if(i==temp_str.length-1){
                stringList.add(temp_str[i].replace("]", ""));
            }else{
                stringList.add(temp_str[i]);
            }
        }            
        return stringList;
    }
        
    //解析[,,,];;[,,,];;为List<List<String>>
    public static List<List<String>> getLList(String str,String separator){
        List<List<String>> strLists  = new ArrayList<List<String>>();
        String[] temp_strL = str.split(separator);
        for (String st:temp_strL){
            List<String> stringList = getStrList(st);
            strLists.add(stringList);
        }
        return strLists;
    }

      
//    public static void main(String[] args) throws Exception {
//        seeExcel();
//
//    
//    }
    
    
}
